/*
 * Created on 2006-2-20
 *
 * TODO To change the template for this generated file go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
package com.hitrust.gs.net;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.hitrust.gs.lib.GSLogWriter;
import com.hitrust.gs.lib.db.GSBaseDAO;
import com.hitrust.gs.util.exception.GSException;


/**
 * @author simonyi
 *
 * TODO To change the template for this generated type comment go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
public class GSAuditDAO extends GSBaseDAO {
    

    /**
     * 从订单明细表中获得原始对帐数据对象
     * @param orderDetailId
     * @return
     * @throws GSException
     */
    public ArrayList queryAuditDetail(Connection conn, String date, int type) throws GSException{
        
        ArrayList tList = new ArrayList();
        GSAuditBean tGSAuditBean = null;
        //状态 ：订货中
        StringBuffer tSql = new StringBuffer("select a.GS_ORDER_DETAIL_ID as P0, ");
            tSql.append(" b.GS_MANUFACTURE_ID as P1,a.GS_ORDER_COUNT as P10, ");
            tSql.append(" a.GS_DELIVER_GOODS_COUNTS as P11,a.GS_SIGNIN_COUNT as P12, ");
            tSql.append(" b.GS_MANUFACTURE_UNITPRICE as P3,a.GS_ORDER_TEMPID as P4 ");
            tSql.append(" from GS_ORDER_DETAIL a,GS_MANUFACTURE b");
            tSql.append(" where a.GS_MANUFACTURE_ID= b.GS_MANUFACTURE_ID and ");
            
        switch(type){//type参数为数据库中对应状态的值
        case 9://ALLITEMS
            tSql.append(" a.GS_ORDER_DETAIL_STATUS >=9" );
            tSql.append(" and replace(SUBSTR(a.GS_SUBMIT_DATE2,1,10),'-','') = '" + date.replaceAll("/","-") + "'");break;
        case 10://ITEMSDOING
            tSql.append(" a.GS_ORDER_DETAIL_STATUS >=10 and a.GS_ORDER_DETAIL_STATUS <> 11");
            tSql.append(" and replace(SUBSTR(a.GS_DELIVER_GOODS_CONFIRM_DATE,1,10),'-','') = '" + date.replaceAll("/","-") + "'");break;
        case 11://ITEMSREFUSED
            tSql.append(" a.GS_ORDER_DETAIL_STATUS >=11");
            tSql.append(" and replace(SUBSTR(a.GS_REJECT_DATE,1,10),'-','') = '" + date.replaceAll("/","-") + "'");break;
        case 12://ITEMSFINISHED
            tSql.append(" a.GS_ORDER_DETAIL_STATUS >=12");
            tSql.append(" and replace(SUBSTR(a.GS_SIGNIN_DATE,1,10),'-','') = '" + date.replaceAll("/","-") + "'");break;
        case 21://ITEMSCANCELED
            tSql.append(" a.GS_ORDER_DETAIL_STATUS >=21");
            tSql.append(" and replace(SUBSTR(a.GS_CANCEL_DATE,1,10),'-','') = '" + date.replaceAll("/","-") + "'");break;
        }
        try{
            tList = executeQuery(conn,new GSAuditBean(),tSql.toString(),null);
//            if(tList.size() > 0){
//                tGSAuditBean =(GSAuditBean)tList.get(0);
//            }
        }catch(Exception e){
            GSLogWriter.error(this,e.toString());
            throw new GSException(GSException.E17781,GSException.E17781_MSG);
        }
        return tList;
    }
    
    /**
     * 获得某日的所有对帐明细
     * @param date
     * @return
     * @throws GSException
     */
    public ArrayList getAuditMsg(String date,String category) throws GSException {
        String tSql = "select * from gs_audit_msg where gs_audit_date=? and gs_audit_category=?";
        ArrayList tList = new ArrayList();
        Object[] tObj = {date,category};
        try{
            tList = executeQuery(new GSAuditMsgBean(),tSql.toString(),tObj);
        }catch(Exception e){
            GSLogWriter.error(this,e.toString());
            throw new GSException(GSException.E17782,GSException.E17782_MSG);
        }
        return tList;
    }
    
    public boolean isTodayAuditFinished(String date) throws GSException {
        String sql1 = "select count(gs_audit_date) from gs_audit_result where gs_audit_date=?";
        Object[] tObj1 = {date};
        Connection conn = null;
        try{
            conn = this.getConnetion();
            conn.setAutoCommit(false);
            PreparedStatement pstmt = conn.prepareStatement(sql1);
            pstmt.setString(1,date);
            ResultSet rs = pstmt.executeQuery();
            rs.next();int temp = rs.getInt(1);
            rs.close();pstmt.close();
            if(temp == 0)
                return false;
            else return true;
        }catch(SQLException e){
            try{conn.setAutoCommit(true);}catch(SQLException ee){}
            throw new GSException (GSException.E17783,GSException.E17783_MSG);
        }finally{
            close();
        }
        
    }
    /**
     * 初始化对帐结果表，初始状态为'未初始化'
     * @param date
     * @throws GSException
     */
    public void initAuditResult(String date) throws GSException {
        String sql1 = "select count(gs_audit_date) from gs_audit_result where gs_audit_date=?";
        Object[] tObj1 = {date};
        
        
        StringBuffer tSql = new StringBuffer("insert into gs_audit_result (gs_audit_date,gs_audit_all_items,");
        		tSql.append("gs_audit_items_doing,gs_audit_items_refused,gs_audit_items_finished,gs_audit_items_canceled)");
        		tSql.append("values (?,?,?,?,?,?)");
        Object[] tObj = {date,"0","0","0","0","0"};
        String sql2 = "delete from gs_audit_msg where gs_audit_date='"+ date + "'";
        
        //
        Connection conn = null;
        try{
            conn = this.getConnetion();
            conn.setAutoCommit(false);
            PreparedStatement pstmt = conn.prepareStatement(sql1);
            pstmt.setString(1,date);
            ResultSet rs = pstmt.executeQuery();
            rs.next();int temp = rs.getInt(1);
            rs.close();pstmt.close();
            //如果数据库中已经存在对帐结果，则不再作初始化
            if(temp == 0) {
                executeUpdate(tSql.toString(),tObj);
            }
            //清理对帐信息表
            pstmt = conn.prepareStatement(sql2);
            pstmt.executeUpdate();
            conn.commit();
        }catch(SQLException e){
            try{conn.setAutoCommit(true);}catch(SQLException ee){}
            throw new GSException (GSException.E17783,GSException.E17783_MSG);
        }finally{
            close();
        }
        
    }
    
    /**
     * 记录对帐统计信息
     * @param conn 数据库联接
     * @param date 对帐日期
     * @param category 对帐类型
     * @param count 产品数量/送货数量/交货数量
     * @param price 产品总价
     * @param statistic 采购网回复的产品数量/送货数量/交货数量+产品总价
     * @param size 订单明细数量
     * @throws GSException 
     */
    void recordAuditStat(Connection conn,String date,int category,int count,double price,String[] statistic,int size) throws GSException {
        String tSql = "";
        Object[] tObj = null;
        switch (category){
        case 1:
            tSql = "update GS_AUDIT_RESULT SET gs_audit_count1=?,gs_audit_count2=?,gs_audit_total_price1=?,gs_audit_product_count1=?,gs_audit_total_price2=?,gs_audit_product_count2=? where gs_audit_date=?";
            tObj = new Object[]{new Integer(size),statistic[0],new Double(price),new Integer(count),statistic[2],statistic[1],date};
            break;
        case 2:
            tSql = "update GS_AUDIT_RESULT SET gs_audit_send_price1=?,gs_audit_send_count1=?,gs_audit_send_price2=?,gs_audit_send_count2=? where gs_audit_date=?";
            tObj = new Object[]{new Double(price),new Integer(count),statistic[2],statistic[1],date};
            break;
        case 3:
            tSql = "update GS_AUDIT_RESULT SET gs_audit_refused_price1=?,gs_audit_refused_count1=?,gs_audit_refused_price2=?,gs_audit_refused_count2=? where gs_audit_date=?";
            tObj = new Object[]{new Double(price),new Integer(count),statistic[2],statistic[1],date};
            break;
        case 4:
            tSql = "update GS_AUDIT_RESULT SET gs_audit_received_price1=?,gs_audit_received_count1=?,gs_audit_received_price2=?,gs_audit_received_count2=? where gs_audit_date=?";
            tObj = new Object[]{new Double(price),new Integer(count),statistic[2],statistic[1],date};
            break;
        case 5:
            tSql = "update GS_AUDIT_RESULT SET gs_audit_canceled_price1=?,gs_audit_canceled_count1=?,gs_audit_canceled_price2=?,gs_audit_canceled_count2=? where gs_audit_date=?";
            tObj = new Object[]{new Double(price),new Integer(count),statistic[2],statistic[1],date};
            break;
        }       
        

        try{
            executeUpdate(conn,tSql,tObj);
        }catch(SQLException e){
            throw new GSException (GSException.E17784,GSException.E17784_MSG);
        }
        
    }
    
    /**
     * 记录对帐状态（匹配）
     * @param conn
     * @param date
     * @param category
     * @throws GSException
     */
    void recordAuditMatchResult(Connection conn,String date,int category) throws GSException {
        String tSql = "";
        Object[] tObj = null;
        switch (category){
        case 1:
            tSql = "update GS_AUDIT_RESULT SET gs_audit_all_items=2 where gs_audit_date=?";
            tObj = new Object[]{date};
            break;
        case 2:
            tSql = "update GS_AUDIT_RESULT SET gs_audit_items_doing=2 where gs_audit_date=?";
            tObj = new Object[]{date};
            break;
        case 3:
            tSql = "update GS_AUDIT_RESULT SET gs_audit_items_refused=2 where gs_audit_date=?";
            tObj = new Object[]{date};
            break;
        case 4:
            tSql = "update GS_AUDIT_RESULT SET gs_audit_items_finished=2 where gs_audit_date=?";
            tObj = new Object[]{date};
            break;
        case 5:
            tSql = "update GS_AUDIT_RESULT SET gs_audit_items_canceled=2 where gs_audit_date=?";
            tObj = new Object[]{date};
            break;
        }       
        

        try{
            executeUpdate(conn,tSql,tObj);
        }catch(SQLException e){
            throw new GSException (GSException.E17785,GSException.E17785_MSG);
        }
        
    }
    /**
     * 记录对帐状态（不匹配）
     * @param conn
     * @param date
     * @param category
     * @throws GSException
     */
    void recordAuditNotMatchResult(Connection conn, String date ,int category) throws GSException {
        String tSql = "";
        switch (category){
        case 1:
            tSql = "update GS_AUDIT_RESULT SET gs_audit_all_items=4 where gs_audit_date=?";
            break;
        case 2:
            tSql = "update GS_AUDIT_RESULT SET gs_audit_items_doing=4 where gs_audit_date=?";
            break;
        case 3:
            tSql = "update GS_AUDIT_RESULT SET gs_audit_items_refused=4 where gs_audit_date=?";
            break;
        case 4:
            tSql = "update GS_AUDIT_RESULT SET gs_audit_items_finished=4 where gs_audit_date=?";
            break;
        case 5:
            tSql = "update GS_AUDIT_RESULT SET gs_audit_items_canceled=4 where gs_audit_date=?";
            break;
        }       
        
        Object[] tObj = {date};
        try{
            executeUpdate(conn,tSql,tObj);
        }catch(SQLException e){
            throw new GSException (GSException.E17785,GSException.E17785_MSG);
        }
    }
    
    private GSAuditBean buildTempBean() {
        GSAuditBean aBean = new GSAuditBean();
        aBean.setManufactureCode("");
        aBean.setManufactureReceivePrice("");
        aBean.setManufactureSendPrice("");
        aBean.setManufactureTotalPrice("");
        aBean.setManufactureUnitPrice("");
        aBean.setOrderCount("");
        aBean.setOrderDetailID("");
        aBean.setOrderTempID("");
        aBean.setReceiveCount("");
        aBean.setSendCount("");
        
        return aBean;
    }
    /**
     * 记录有差错的对帐信息
     * @param aBean
     * @param bBean
     * @param category
     * @throws GSException
     */
    void recordAuditNotMatchMsg(Connection conn,GSAuditBean aBean,GSAuditBean bBean,String date,int category) throws GSException {
        StringBuffer tSql = new StringBuffer("insert into GS_AUDIT_MSG ( GS_AUDIT_DATE,GS_AUDIT_CATEGORY,");
        		tSql.append(" GS_AUDIT_DETAIL_ID1,GS_AUDIT_MANUFACTURE_ID1,GS_AUDIT_COUNT1,GS_AUDIT_UNITPRICE1,");
        		tSql.append(" GS_AUDIT_TOTALPRICE1,GS_AUDIT_DETAIL_ID2,GS_AUDIT_MANUFACTURE_ID2,");
        		tSql.append(" GS_AUDIT_COUNT2,GS_AUDIT_UNITPRICE2,GS_AUDIT_TOTALPRICE2");
        		tSql.append(" ) values (?,?,?,?,?,?,?,?,?,?,?,?)");
        		
        String aCount = null;String bCount = null;
        if(aBean == null){
            aBean = buildTempBean();
        }else if(bBean == null){
            bBean = buildTempBean();
        }
        
        switch (category){
        case 1:
            aCount = aBean.getOrderCount();bCount = bBean.getOrderCount();
            break;
        case 2:
            aCount = aBean.getSendCount();bCount = bBean.getSendCount();
            break;
        case 3:
            aCount = aBean.getOrderCount();bCount = bBean.getOrderCount();
            break;
        case 4:
            aCount = aBean.getReceiveCount();bCount = bBean.getReceiveCount();
            break;
        case 5:
            aCount = aBean.getOrderCount();bCount = bBean.getOrderCount();
        }
        
        Object[] tObj = {date,""+category,
                aBean.getOrderDetailID(),aBean.getManufactureCode(),aCount,
                aBean.getManufactureUnitPrice(),aBean.getManufactureTotalPrice(),
		        bBean.getOrderDetailID(),bBean.getManufactureCode(),bCount,
		        bBean.getManufactureUnitPrice(),bBean.getManufactureTotalPrice()
        };
    
        try{
            executeUpdate(conn,tSql.toString(),tObj);
        }catch(SQLException e){
            throw new GSException (GSException.E17786,GSException.E17786_MSG);
        }

    }

    /**
     * 用户重新执行下载，根据下载文件类型，把相应的数据库对帐结果置为0(未对帐)
     * @param date 对帐日期
     * @param category 对帐类型
     * @throws GSException
     */
    public void reDownload(String date,int category) throws GSException {
        String tSql = "";
        switch (category){
        case 1:
            tSql = "update GS_AUDIT_RESULT set gs_audit_all_items = 0 where gs_audit_date = ? ";
            break;
        case 2:
            tSql = "update GS_AUDIT_RESULT set gs_audit_items_doing = 0 where gs_audit_date = ? ";
            break;
        case 3:
            tSql = "update GS_AUDIT_RESULT set gs_audit_items_refused = 0 where gs_audit_date = ? ";
            break;
        case 4:
            tSql = "update GS_AUDIT_RESULT set gs_audit_items_finished = 0 where gs_audit_date = ? ";
            break;
        case 5:
            tSql = "update GS_AUDIT_RESULT set gs_audit_items_canceled = 0 where gs_audit_date = ? ";
            break;
        }       
        
        Object[] obj = {date};
        try{
            executeUpdate(tSql,obj);
        }catch(SQLException e){
            throw new GSException(GSException.E17787,GSException.E17787_MSG);
        }
                
    }
    
    public GSAuditResultBean getAuditResult(String date) throws GSException {
        String tSql = "select * from gs_audit_result where gs_audit_date=?";
        GSAuditResultBean tGSAuditResultBean = null;
        Object[] obj = {date};
        ArrayList tList = null;
        try{
            tList = executeQuery(new GSAuditResultBean(),tSql,obj);
            if(tList.size() > 0)
                tGSAuditResultBean = (GSAuditResultBean)tList.get(0);
        }catch(Exception e){
            GSLogWriter.error(this,e.toString());
            throw new GSException(GSException.E19999,GSException.E19999_MSG);
        }
        return tGSAuditResultBean;
    }
    
    public void recordDownloadSucceed(String date,int category) throws GSException {
        String tSql = "";
        switch (category){
        case 1:
            tSql = "update GS_AUDIT_RESULT set gs_audit_all_items = 1 where gs_audit_date = ? ";
            break;
        case 2:
            tSql = "update GS_AUDIT_RESULT set gs_audit_items_doing = 1 where gs_audit_date = ? ";
            break;
        case 3:
            tSql = "update GS_AUDIT_RESULT set gs_audit_items_refused = 1 where gs_audit_date = ? ";
            break;
        case 4:
            tSql = "update GS_AUDIT_RESULT set gs_audit_items_finished = 1 where gs_audit_date = ? ";
            break;
        case 5:
            tSql = "update GS_AUDIT_RESULT set gs_audit_items_canceled = 1 where gs_audit_date = ? ";
            break;
        }       
        
        Object[] obj = {date};
        try{
            executeUpdate(tSql,obj);
        }catch(SQLException e){
            throw new GSException(GSException.E17788,GSException.E17788_MSG);
        }

    }
    public void recordDownloadFailed(String date,int category) throws GSException {
        String tSql = "";
        switch (category){
        case 1:
            tSql = "update GS_AUDIT_RESULT set gs_audit_all_items = 3 where gs_audit_date = ? ";
            break;
        case 2:
            tSql = "update GS_AUDIT_RESULT set gs_audit_items_doing = 3 where gs_audit_date = ? ";
            break;
        case 3:
            tSql = "update GS_AUDIT_RESULT set gs_audit_items_refused = 3 where gs_audit_date = ? ";
            break;
        case 4:
            tSql = "update GS_AUDIT_RESULT set gs_audit_items_finished = 3 where gs_audit_date = ? ";
            break;
        case 5:
            tSql = "update GS_AUDIT_RESULT set gs_audit_items_canceled = 3 where gs_audit_date = ? ";
            break;
        }       
        
        Object[] obj = {date};
        try{
            executeUpdate(tSql,obj);
        }catch(SQLException e){
            throw new GSException(GSException.E17788,GSException.E17788_MSG);
        }
        
    }
    
    /**
     * 清除原对帐结果和差错数据
     * @param date 对帐日期
     * @param category 对帐类型
     * @throws GSException
     */
    public void clearOverdueMsg(String date,int category) throws GSException {
        String tSql1 = "delete from GS_AUDIT_MSG where gs_audit_date = ? and gs_audit_category = ?";
        String tSql2 = "";
        switch (category){
        case 1:
            tSql2 = "update GS_AUDIT_RESULT set gs_audit_all_items = 0 where gs_audit_date = ? ";
            break;
        case 2:
            tSql2 = "update GS_AUDIT_RESULT set gs_audit_items_doing = 0 where gs_audit_date = ? ";
            break;
        case 3:
            tSql2 = "update GS_AUDIT_RESULT set gs_audit_items_refused = 0 where gs_audit_date = ? ";
            break;
        case 4:
            tSql2 = "update GS_AUDIT_RESULT set gs_audit_items_finished = 0 where gs_audit_date = ? ";
            break;
        case 5:
            tSql2 = "update GS_AUDIT_RESULT set gs_audit_items_canceled = 0 where gs_audit_date = ? ";
            break;
        }       
        
        Object[] obj1 = {date,Integer.toString(category)};
        Object[] obj2 = {date};
        Connection conn = null;
        try{
            conn = this.getConnetion();
            conn.setAutoCommit(false);
            executeUpdate(tSql1,obj1);
            executeUpdate(tSql2,obj2);
            conn.commit();
        }catch(SQLException e){
            try{
            conn.rollback();
            }catch(SQLException sqle){}
            throw new GSException(GSException.E00001,GSException.E00001_MSG);
        }finally{
            try{
                conn.setAutoCommit(true);            
            }catch(SQLException sqle){}
            GSAuditDAO.close(conn);
        }
        
    }

}
